﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VSDBM.Code.dto;
using System.Data;
using MySql.Data.MySqlClient;

namespace VSDBM.Code.dao
{
    public class SubtypeDAO
    {

        public static String add(Subtype subtype)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();

                DataSet ds = select(" WHERE description = '" + subtype.descricao + "' AND status = 1 AND idorganisms = "+ subtype.idOrganism, "");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    MySqlCommand query = new MySqlCommand("INSERT INTO subtypes (description,idorganisms,status) VALUES ('" + subtype.descricao + "'," + subtype.idOrganism + ",1)", connection);
                    query.ExecuteNonQuery();
                    return "Added!";
                }

                return "Subtype description '" + subtype.descricao + "' already exist.";
            }
            catch (Exception e)
            {
                return "ERRO! - " + e.Message;
            }
        }

        public static Boolean delete(Subtype subtype)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query2 = new MySqlCommand("DELETE FROM subtypes_references_sequences WHERE idsubtype = " + subtype.id + "", connection);
                query2.ExecuteNonQuery();
                MySqlCommand query1 = new MySqlCommand("DELETE FROM sequence_subtype WHERE idsubtype = " + subtype.id + "", connection);
                query1.ExecuteNonQuery();
                MySqlCommand query = new MySqlCommand("DELETE FROM subtypes WHERE id = " + subtype.id + "", connection);
                query.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public static void setActive(int id)
        {
            MySqlConnection connection = Connection.getConnection();
            MySqlCommand query = new MySqlCommand("UPDATE subtypes SET status=1 WHERE id=" + id, connection);
            try
            {
                query.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static DataSet select(String filters, String orderby)
        {

            DataSet dSet = new DataSet();
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT * FROM subtypes " + filters + " " + orderby, connection);
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                return dSet;
            }
        }

        public static DataSet selectforgridviewer( String filtros)
        {

            DataSet dSet = new DataSet();
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT sub.id, sub.description, org.name as organism FROM subtypes sub JOIN organisms org ON (sub.idorganisms = org.id) WHERE sub.status <> 0 " + filtros, connection);
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                return dSet;
            }
        }

        public static Subtype Fill(int id, String description,int status)
        {
            Subtype subtype = new Subtype();
            subtype.id = id;
            subtype.descricao = description;
            subtype.status = status;
            return subtype;
        }

        public static List<Subtype> DStoList(DataSet ds)
        {
            List<Subtype> list = new List<Subtype>();
            try
            {
                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    DataTable registro = ds.Tables[0];
                    list.Add(Fill(int.Parse(registro.Rows[i]["id"].ToString()), registro.Rows[i]["description"].ToString(), int.Parse(registro.Rows[i]["status"].ToString())));
                }

            }
            catch (Exception e) { }
            return list;
        }







    }
}